﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;

namespace DAO
{
    public class MatHangDAO
    {
       // ------------------ LOI ---------------------
        public List<MatHangDTO> LayDanhSachMatHangL()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT * FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();

                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.MaXuatXu = reader.GetInt32(1);
                mhDTO.TenMatHang = reader.GetString(2);
                mhDTO.SoLuong = reader.GetInt32(3);
                mhDTO.GiaNhap = reader.GetDouble(4);
                mhDTO.MaDonViTinh = reader.GetInt32(5);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<MatHangDTO> LayDanhSachTenMatHangL()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT DISTINCT MaMatHang, TenMatHang FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();
                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.TenMatHang = reader.GetString(1);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<XuatXuDTO> LayXuatXuTheoMatHangL(int maMH)
        {
            List<XuatXuDTO> danhsach = new List<XuatXuDTO>();
            string sql = "Select xx.MaXuatXu, xx.TenXuatXu From XUATXU xx, MATHANG mh Where xx.MaXuatXu = mh.MaXuatXu and mh.MaMatHang = " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                XuatXuDTO xxDTO = new XuatXuDTO();
                xxDTO.MaXuatXu = reader.GetInt32(0);
                xxDTO.TenXuatXu = reader.GetString(1);
                danhsach.Add(xxDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int LaySoLuongTheoMatHangL(int maMH, int maXX)
        {
            int soluong = 0;
            string sql = "Select SoLuong From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                soluong = reader.GetInt32(0);
            }
            conn.Close();
            return soluong;
        }
        public double LayGiaTheoMatHangL(int maMH, int maXX)
        {
            double gia = 0;
            string sql = "Select GiaNhap From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                gia = reader.GetDouble(0);
            }
            conn.Close();
            return gia;
        }
        public string LayTenMatHangTheoMaL(int maMH)
        {
            string tenmh = "";
            string sql = "Select TenMatHang From MATHANG Where MaMatHang= " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tenmh = reader.GetString(0);
            }
            conn.Close();
            return tenmh;
        }
        public int LayMaMatHangTheoTenL(string ten)
        {
            int ma = 0;
            string sql = "Select MaMatHang From MATHANG Where TenMatHang= " + "N'" + ten + "'";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ma = reader.GetInt32(0);
            }
            conn.Close();
            return ma;
        }
        public string CapNhatSoLuongL(int maMH, int maXX, int soluong, bool err)
        {
            string mess = "";
            int tonkho = LaySoLuongTheoMatHangL(maMH, maXX);
            if (err)
                tonkho = tonkho + soluong;
            else
                tonkho = tonkho - soluong;
            string sql = "Update MATHANG SET SoLuong = " + tonkho + " Where MaMatHang = " + maMH + " and MaXuatXu = " + maXX;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        // END --------------- LOI -----------------------
        // ----------------KIM------------------
        public List<MatHangDTO> KLayDSMH()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT * FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();

                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.MaXuatXu = reader.GetInt32(1);
                mhDTO.TenMatHang = reader.GetString(2);
                mhDTO.SoLuong = reader.GetInt32(3);
                mhDTO.GiaNhap = reader.GetDouble(4);
                mhDTO.MaDonViTinh = reader.GetInt32(5);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<MatHangDTO> KLayDanhSachTenMatHang()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT DISTINCT MaMatHang, TenMatHang FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();
                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.TenMatHang = reader.GetString(1);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<XuatXuDTO> KLayXuatXuTheoMatHang(int maMH)
        {
            List<XuatXuDTO> danhsach = new List<XuatXuDTO>();
            string sql = "Select xx.MaXuatXu, xx.TenXuatXu From XUATXU xx, MATHANG mh Where xx.MaXuatXu = mh.MaXuatXu and mh.MaMatHang = " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                XuatXuDTO xxDTO = new XuatXuDTO();
                xxDTO.MaXuatXu = reader.GetInt32(0);
                xxDTO.TenXuatXu = reader.GetString(1);
                danhsach.Add(xxDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int KLaySoLuongTheoMatHang(int maMH, int maXX)
        {
            int soluong = 0;
            string sql = "Select SoLuong From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                soluong = reader.GetInt32(0);
            }
            conn.Close();
            return soluong;
        }
        public string KLayTenMatHangTheoMa(int maMH)
        {
            string tenmh = "";
            string sql = "Select TenMatHang From MATHANG Where MaMatHang= " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tenmh = reader.GetString(0);
            }
            conn.Close();
            return tenmh;
        }
        public int KLayMaMatHangTheoTen(string ten)
        {
            int ma = 0;
            string sql = "Select MaMatHang From MATHANG Where TenMatHang= " + "N'" + ten + "'";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ma = reader.GetInt32(0);
            }
            conn.Close();
            return ma;
        }
        public int LaySoLuongTheoMatHangK(int maMH, int maXX)
        {
            int soluong = 0;
            string sql = "Select SoLuong From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                soluong = reader.GetInt32(0);
            }
            conn.Close();
            return soluong;
        }
        public string KCapNhatSoLuong(int maMH, int maXX, int soluong, bool err)
        {
            string mess = "";
            int tonkho = LaySoLuongTheoMatHangK(maMH, maXX);
            if (err)
                tonkho = tonkho + soluong;
            else
                tonkho = tonkho - soluong;
            string sql = "Update MATHANG SET SoLuong = " + tonkho + " Where MaMatHang = " + maMH + " and MaXuatXu = " + maXX;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        public int KLayMaMatHang()
        {
            int max = 0;
            List<MatHangDTO> listKH = KLayDSMH();
            if (listKH.Count < 1)
                return max;
            for (int i = 0; i < listKH.Count; i++)
            {
                if (listKH[i].MaMatHang > max)
                    max = listKH[i].MaMatHang;
            }
            return max;
        }
        public string KCapNhatGiaNhap(int maMH, int maXX, double gianhap, bool err)
        {
            string mess = "";

            string sql = "Update MATHANG SET GiaNhap = " + gianhap + " Where MaMatHang = " + maMH + " and MaXuatXu = " + maXX;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        public double KLayGiaTheoMatHang(int maMH, int maXX)
        {
            double gia = 0;
            string sql = "Select GiaNhap From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                gia = reader.GetDouble(0);
            }
            conn.Close();
            return gia;
        }
        //END-----------------KIM-----------------
        //---------------------HAI-------------------
        public List<MatHangDTO> LayDSMH_H()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT * FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();

                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.MaXuatXu = reader.GetInt32(1);
                mhDTO.TenMatHang = reader.GetString(2);
                mhDTO.SoLuong = reader.GetInt32(3);
                mhDTO.GiaNhap = reader.GetDouble(4);
                mhDTO.MaDonViTinh = reader.GetInt32(5);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<MatHangDTO> LayDanhSachTenMatHang()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT DISTINCT MaMatHang, TenMatHang FROM MATHANG";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            List<MatHangDTO> listMH = new List<MatHangDTO>();

            while (reader.Read())
            {
                MatHangDTO mhDTO = new MatHangDTO();
                mhDTO.MaMatHang = reader.GetInt32(0);
                mhDTO.TenMatHang = reader.GetString(1);
                listMH.Add(mhDTO);
            }

            conn.Close();
            return listMH;
        }
        public List<XuatXuDTO> LayXuatXuTheoMatHang(int maMH)
        {
            List<XuatXuDTO> danhsach = new List<XuatXuDTO>();
            string sql = "Select xx.MaXuatXu, xx.TenXuatXu From XUATXU xx, MATHANG mh Where xx.MaXuatXu = mh.MaXuatXu and mh.MaMatHang = " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                XuatXuDTO xxDTO = new XuatXuDTO();
                xxDTO.MaXuatXu = reader.GetInt32(0);
                xxDTO.TenXuatXu = reader.GetString(1);
                danhsach.Add(xxDTO);
            }
            conn.Close();
            return danhsach;
        }
        public int LaySoLuongTheoMatHang(int maMH, int maXX)
        {
            int soluong = 0;
            string sql = "Select SoLuong From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                soluong = reader.GetInt32(0);
            }
            conn.Close();
            return soluong;
        }
        public double LayGiaTheoMatHang(int maMH, int maXX)
        {
            double gia = 0;
            string sql = "Select GiaNhap From MATHANG Where MaMatHang= " + maMH + "and MaXuatXu = " + maXX;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                gia = reader.GetDouble(0);
            }
            conn.Close();
            return gia;
        }
        public string LayTenMatHangTheoMa(int maMH)
        {
            string tenmh = "";
            string sql = "Select TenMatHang From MATHANG Where MaMatHang= " + maMH;

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                tenmh = reader.GetString(0);
            }
            conn.Close();
            return tenmh;
        }
        public int LayMaMatHangTheoTen(string ten)
        {
            int ma = 0;
            string sql = "Select MaMatHang From MATHANG Where TenMatHang= " + "N'" + ten + "'";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ma = reader.GetInt32(0);
            }
            conn.Close();
            return ma;
        }
        public string CapNhatSoLuong(int maMH, int maXX, int soluong, bool err)
        {
            string mess = "";
            int tonkho = LaySoLuongTheoMatHang(maMH, maXX);
            if (err)
                tonkho = tonkho + soluong;
            else
                tonkho = tonkho - soluong;
            string sql = "Update MATHANG SET SoLuong = " + tonkho + " Where MaMatHang = " + maMH + " and MaX = " + maXX;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        public int LayMaMatHang()
        {
            int max = 0;
            List<MatHangDTO> listKH = LayDSMH_H();
            if (listKH.Count < 1)
                return max;
            for (int i = 0; i < listKH.Count; i++)
            {
                if (listKH[i].MaMatHang > max)
                    max = listKH[i].MaMatHang;
            }
            return max;
        }
        DataProvider dp = new DataProvider();
        public DataTable MatHang()
        {
            return dp.GetData("select * from MatHang");
        }
        public void insertmh(MatHangDTO mh)
        {
            dp.ExeCuteNonQuery("insert into MatHang(MaMatHang, TenMatHang, MaXuatXu, SoLuong, GiaNhap, MaDonViTinh, GhiChu) values(" + mh.MaMatHang + ",N'" + mh.TenMatHang + "',N'" + mh.MaXuatXu + "',N'" +
                mh.SoLuong + "',N'" + mh.GiaNhap + "',N'" + mh.MaDonViTinh + "',N'" + mh.GhiChu + "')");
        }
        public void updatemh(MatHangDTO mh)
        {
            dp.ExeCuteNonQuery("update MatHang set TenMatHang = '" + mh.TenMatHang + "', MaDonViTinh = " + mh.MaDonViTinh + ",SoLuong =" + mh.SoLuong + ",GiaNhap = " +
              mh.GiaNhap + ",GhiChu = N'" + mh.GhiChu + "' where MaMatHang = " + mh.MaMatHang + " and XuatXu = " + mh.MaXuatXu);
        }
        public string delete(int mh)
        {
            string mess = "";
            string sql = "Delete * From HOADON where MaHoaDon = " + mh;
            SqlConnection conn = DataProvider.ConnectDB();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return mess;
        }
        //----------------------END HAI----------------
    }
}
